package persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Vector;

import persistencia.Broker;
import persistencia.Conexion;

import dominio.*;

public class RevisorDAO {

	public static Revisor newRevisor(String dni) throws Exception {
		Revisor result = new Revisor();
		// String[] pclaves=getPclaves(dni);
		// result.setKeywords(pclaves);
		String SQL = "Select dni, nombre, apellido, afiliacion, email, rol, pass from Persona where dni=?";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, dni);
			ResultSet r = p.executeQuery();
			if (r.next()) {
				result.setDni(r.getString(1));
				result.setNombre(r.getString(2));
				result.setApellido(r.getString(3));
				result.setAfiliacion(r.getString(4));
				result.setEmail(r.getString(5));
				result.setRol(r.getString(6));
				result.setPassword(r.getString(7));
			} else {
				throw new Exception("La persona con DNI " + dni + " no existe");
			}
		} catch (Exception e) {
		}

		finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static String getPclaves(String dni) throws Exception {
		String result = "";
		String SQL = "Select idPalabra from PalabraRevisor where idRevisor=?";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, dni);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				result = result + r.getString("idPalabra") + " ";
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static void update(Revisor rev, String clav) throws Exception {
		RevisorDAO.updateClave(rev.getDni(), clav);
		PersonaDAO.update(rev);
	}

	public static void updateClave(String dni, String clav) throws Exception {
		// Borrar las palabras clave antiguas
		String SQL = "Delete from PalabraRevisor where idRevisor=?";
		String[] tokens = clav.split(",( )*");
		int num = tokens.length;
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, dni);
			p.executeUpdate();
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		// Insertar las palabras clave en PalabrasClave y en PalabrasRevisor
		for (int i = 0; i < num; i++) {
			String SQL2 = "Insert ignore into PalabrasClave (PalabrasClave) values (?)";
			String SQL3 = "Insert into PalabraRevisor (idPalabra,idRevisor) values (?,?)";
			bd = null;
			ex = null;
			p = null;
			try {
				bd = Broker.get().getBD();
				p = bd.prepareStatement(SQL2);
				p.setString(1, tokens[i]);
				p.executeUpdate();
			} catch (Exception e) {
				ex = e;
			} finally {
				bd.close();
				if (ex != null)
					throw ex;
			}
			try {
				bd = Broker.get().getBD();
				p = bd.prepareStatement(SQL3);
				p.setString(1, tokens[i]);
				p.setString(2, dni);
				p.executeUpdate();
			} catch (Exception e) {
				ex = e;
			} finally {
				bd.close();
				if (ex != null)
					throw ex;
			}
		}
	}

	public static void insert(Revisor revisor) throws Exception {
		PersonaDAO.insert(revisor);
		String SQL = "Insert into Revisor (dni) values (?)";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, revisor.getDni());
			p.executeUpdate();
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
	}

	public static void delete(Revisor revisor) throws Exception {
		PersonaDAO.delete(revisor);
	}

	public static Vector<Revisor> getRecomendaciones() throws Exception {
		Vector<Revisor> result = new Vector<Revisor>();
		String SQL = "Select dni from Revisor";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				Revisor revisor = newRevisor(r.getString(1));
				result.add(revisor);
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static Vector<Invitacion> invitacionRevisor(String revisor)
			throws Exception {
		Vector<Invitacion> result = new Vector<Invitacion>();
		String SQL = "Select * from Invitacion where idRevisor=? and estado='RECIBIDA'";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, revisor);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				int id = Integer.parseInt(r.getString(4));
				Invitacion i = InvitacionDAO.newInvitacion(id);
				result.add(i);
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static Vector<Articulo> articuloRevisor(String revisor)
			throws Exception {
		Vector<Articulo> result = new Vector<Articulo>();
		String SQL = "Select idArticulo from Invitacion i, RevisionArticulo re, Articulo a where i.idRevisor=? and i.estado='ACEPTADA' and i.idArticulo=a.id and re.articulo=a.id and re.estado='INICIADA'";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, revisor);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				int id = Integer.parseInt(r.getString(1));
				Articulo a = ArticuloDAO.newArticulo(id);
				result.add(a);
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static Vector<Revisor> RevisoresPalabraClave(String palabras)
			throws Exception {
		String[] tokens = palabras.split(",( )*");
		String condicion = "";
		for (String t : tokens) {
			condicion += ("idPalabra='" + t.toLowerCase() + "' or ");
		}
		condicion = condicion.substring(0, condicion.length() - 4);

		Vector<Revisor> result = new Vector<Revisor>();

		String SQL;
		if (!palabras.equals(""))
			SQL = "Select * from Persona, PalabraRevisor where (" + condicion
					+ ") and dni=idRevisor group by dni;";
		else
			SQL = "Select * from Persona where rol='Revisor';";

		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				result.add(newRevisor(r.getString("dni")));
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	public static void invitarArticulo(String dni, int articulo, String editor)
			throws Exception {
		// enviar correo revisores
		Calendar today = new GregorianCalendar();
		SimpleDateFormat df = new SimpleDateFormat();
		df.applyPattern("dd-MM-yyyy");

		String SQL = "insert into Invitacion (idArticulo, idRevisor, idEditor, fecha, estado) values (?, ?, ?, ?, ?);";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setInt(1, articulo);
			p.setString(2, dni);
			p.setString(3, editor);
			p.setString(4, df.format(today.getTime()));
			p.setString(5, "RECIBIDA");
			p.executeUpdate();
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}

	}

	public static void castAutorToRevisor(Persona persona) throws Exception {
		persona.setRol("Revisor");
		PersonaDAO.update(persona);

		String SQL = "Insert into Revisor (dni) values (?)";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, persona.getDni());
			p.executeUpdate();
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
	}

	public static Vector<Revisor> getRevisores() throws Exception {
		Vector<Revisor> result = new Vector<Revisor>();
		String SQL = "Select dni from Persona where rol='Revisor'";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			ResultSet r = p.executeQuery();
			while (r.next()) {
				Revisor revisor = newRevisor(r.getString(1));
				result.add(revisor);
			}
		} catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}
}
